﻿Imports System.Data.SqlClient

Public Class ReservaDAO
    Public Function listar() As Collection
        Dim lista As New Collection

        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "SELECT  id_usuario, fecha_reserva, pago,total FROM Reserva"
            Dim cmd As New SqlCommand(sql, cn)

            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader()
            While dr.Read
                Dim modelo As New Reserva
                modelo.idUsuario = dr(0).ToString
                modelo.FechaReserva = dr(1).ToString
                modelo.Pago = dr(2).ToString
                modelo.Total = dr(3).ToString
               
                lista.Add(modelo)
            End While

            dr.Close()
            cn.Close()

        Catch ex As SqlException
            Throw New DAOExcepcion(ex.ToString)
        End Try

        Return lista
    End Function

    Public Function insertar(ByVal modelo As Reserva) As Reserva
        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "INSERT INTO Reserva (id_usuario, fecha_reserva, pago,total) VALUES (@VALOR2, @VALOR3, @VALOR4, @VALOR5)"

            Dim cmd As New SqlCommand(sql, cn)

            cmd.Parameters.AddWithValue("@VALOR2", modelo.idUsuario)
            cmd.Parameters.AddWithValue("@VALOR3", modelo.FechaReserva)
            cmd.Parameters.AddWithValue("@VALOR4", modelo.Pago)
            cmd.Parameters.AddWithValue("@VALOR5", modelo.Total)
            Dim afectados = cmd.ExecuteNonQuery

            If afectados <> 1 Then
                Throw New DAOExcepcion("ReservaDAO: No insertó")
            End If

            ' LEER EL ULTIMO ID
            sql = "SELECT @@IDENTITY"
            cmd = New SqlCommand(sql, cn)
            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader()
            If dr.Read Then
                modelo.idReserva = CType(dr(0), Integer)
            End If

            dr.Close()
            cn.Close()

        Catch ex As Exception
            Throw New DAOExcepcion(ex.ToString)
        End Try

        Return modelo

    End Function

    Public Function buscarPorID(ByVal ID As Integer) As Collection
        Dim lista As New Collection

        Try
            Dim cn As New SqlConnection(ConexionBD.obtenerCadenaConexion())
            cn.Open()

            Dim sql = "SELECT id_reserva, id_usuario, fecha_reserva, pago,total FROM Reserva WHERE id_usuario=@VALOR1"
            Dim cmd As New SqlCommand(sql, cn)
            cmd.Parameters.AddWithValue("@VALOR1", ID)

            Dim dr As SqlDataReader
            dr = cmd.ExecuteReader()

            While dr.Read
                Dim modelo As New Reserva
                modelo.idReserva = dr(0).ToString
                modelo.idUsuario = dr(1).ToString
                modelo.FechaReserva = dr(2).ToString
                modelo.Pago = dr(3).ToString
                modelo.Total = dr(4).ToString
                lista.Add(modelo)

            End While

            dr.Close()
            cn.Close()

        Catch ex As SqlException
            Throw New DAOExcepcion(ex.ToString)
        End Try

        Return lista
    End Function
End Class
